﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace CIT.WMS.Product.Bll
{
    class Bll_ProductIn
    {
        static bool flag;
        static bool result;


        public static DataTable GetInStockProduct()
        {
            string sqlcmd = string.Format(@"SELECT *  FROM [dbo].[wms_T_InStock] where status='0' and ( BillType ='返工入库单')");
            var result = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            return result;
        }

        public static DataTable GetInStockProductByTorr()
        {
            string sqlcmd = string.Format(@"SELECT *  FROM [dbo].[wms_T_InStock] where status='0' and (billtype='成品入库' OR BillType ='手工成品入库')");
            var result = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            return result;
        }
        /// <summary>
        /// 查询单据
        /// </summary>
        /// <param name="varWhere"></param>
        /// <returns></returns>
        public static DataTable Query(string varWhere)
        {
            string sqlcmd = string.Format(@"select a.*,b.Storage_Name from T_Bllb_StorageDoc_tbsd a left join T_Bllb_Storage_tbs b
                                                    on a.Source_Storage =b.Storage_SN {0}", varWhere);
            return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
        /// <summary>
        /// 根据单据查询仓库
        /// </summary>
        /// <param name="varDoc"></param>
        /// <returns></returns>
        public static DataTable QuerySource(string varDoc)
        {
            string strSql = string.Format(@"select Source_Storage from T_Bllb_StorageDoc_tbsd where s_doc_no = '{0}'", varDoc);
            return Utils.NMS.QueryDataTable(Utils.uContext, strSql, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
        /// <summary>
        /// 查询库位是否合格
        /// </summary>
        /// <param name="varLocation"></param>
        /// <returns></returns>
        public static DataTable QueryLocation(string locationSN, string billNo)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_InStock WHERE BillNo='{0}'", billNo);
            var res = Utils.NMS.GetDataTable(sql);

            var storageSN = res.Rows[0]["Storage_SN"].ToString();

            string sqlcmd = string.Format(@"select a.Location_SN,b.Area_SN,c.Storage_SN from T_Bllb_StorageLocation_tbsl a 
left join T_Bllb_StorageArea_tbsa b on a.Area_SN = b.Area_SN
 left join T_Bllb_Storage_tbs c on b.Storage_SN = c.Storage_SN
 LEFT JOIN wms_T_StorageRegion d ON c.Region_SN=d.Region_SN
 WHERE d.Region_Name='{0}' AND a.Location_SN='{1}' AND c.Storage_Type='201'", Utils.uContext.CompanyNamek__BackingField, locationSN);
            if (!string.IsNullOrEmpty(storageSN))
            {
                sqlcmd = sqlcmd + "  and c.Storage_SN='" + storageSN + "'";
            }
            var result = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            return result;
        }

        public static bool CheckLocation(string locationSN)
        {
            string sqlcmd = string.Format(@"select tbsa.Assigned_Type from T_Bllb_StorageLocation_tbsl tbsl inner join T_Bllb_StorageArea_tbsa tbsa 
                                            on tbsa.Area_SN=tbsl.Area_SN where Location_SN='{0}'", locationSN);
            var dt = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            if (dt.Rows.Count == 0)
                return false;

            if (dt.Rows[0]["Assigned_Type"].ToString() != "Y")
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 查询包装箱信息
        /// </summary>
        /// <param name="varBoxSn"></param>
        /// <returns></returns>
        public static DataTable QueryBox(string varBoxSn)
        {
            string strBox = string.Format(@"select container_sn_1,OVER_FLAG,plan_qty,b.Product,b.sfcno,count(1) NUM 
                                                    from T_Bllb_packageOne_tbpo a left join SfcDatProduct b 
                                                    on a.sfc_no = b.sfcno
                                                    where container_sn_1 = '{0}'
                                                    group by container_sn_1,OVER_FLAG,plan_qty,Product,b.sfcno",
                                            varBoxSn);
            return Utils.NMS.QueryDataTable(Utils.uContext, strBox, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
        /// <summary>
        /// 查询包装箱是否已入库
        /// </summary>
        /// <param name="varBoxSn"></param>
        /// <returns></returns>
        public static DataTable QueryIsIn(string instockNo, string boxNo)
        {
            string sqlcmd = string.Format(@"SELECT * FROM dbo.wms_T_InStockRowDetails d
LEFT JOIN dbo.wms_T_InStockRows r ON d.BillNo=r.BillNo AND d.RowNo=r.RowNo
LEFT JOIN dbo.wms_T_InStock s ON r.BillNo=s.BillNo
WHERE s.BillNo='{0}' AND d.BoxNo='{1}'", instockNo, boxNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        /// <summary>
        /// 插入数据库 库存表
        /// </summary>
        /// <param name="varStorage_SN"></param>
        /// <param name="varArea_SN"></param>
        /// <param name="varLocation_SN"></param>
        /// <param name="varMaterialCode"></param>
        /// <param name="varQTY"></param>
        /// <param name="varSerialNumber"></param>
        /// <param name="varDateCode"></param>
        /// <param name="varSfcNo"></param>
        /// <returns></returns>
        public static bool InsertStock(string varStorage_SN, string varArea_SN, string varLocation_SN
            , string varMaterialCode, string varQTY, string varSerialNumber, string varDateCode, string varSfcNo)
        {
            try
            {
                string strInsert = string.Format(@"INSERT INTO T_Bllb_StockInfo_tbsi (Storage_SN, Area_SN, Location_SN, MaterialCode, QTY, serialNumber,DateCode,SfcNo)
                                                 VALUES('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}')",
                                         varStorage_SN, varArea_SN, varLocation_SN, varMaterialCode, varQTY, varSerialNumber, varDateCode, varSfcNo);
                Utils.NMS.Exectransql(Utils.uContext, strInsert, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        //插入单据信息
        public static bool InsertDoc(string varDoc, string varMaterialCode, string varQty, string varSerialNumber, string varDateCode)
        {
            try
            {
                string strMat = string.Format(@"select TSDM_id,qty,plan_qty from T_Bllb_StorageDocMaterial_tsdm 
                                                        where s_doc_no = '{0}' and MaterialCode = '{1}'",
                                                varDoc, varMaterialCode);
                DataTable dtMat = Utils.NMS.QueryDataTable(Utils.uContext, strMat, new CIT.WMS.MESWcf.CmdParameter[0]);
                string tsdm_id = "";
                if (dtMat == null || dtMat.Rows.Count == 0)
                    return false;
                if (dtMat.Rows.Count == 1)
                {
                    tsdm_id = dtMat.Rows[0]["TSDM_id"].ToString();
                }
                foreach (DataRow drMat in dtMat.Rows)
                {
                    if (decimal.Parse(drMat["qty"].ToString()) < decimal.Parse(drMat["plan_qty"].ToString()))
                    {
                        tsdm_id = drMat["TSDM_id"].ToString();
                    }
                }
                //更新中间表
                string strTsdm = string.Format(@"update T_Bllb_StorageDocMaterial_tsdm set qty = qty + {1} where tsdm_id = '{0}'",
                                                tsdm_id, varQty);
                Utils.NMS.Exectransql(Utils.uContext, strTsdm, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
                //更新明细表
                string strDocDel = string.Format(@"INSERT INTO T_Bllb_StorageDocDetail_tbsdd 
                                                    (S_Doc_NO, MaterialCode, QTY, Create_Time, Creator, SerialNumber, DateCode,TSDM_ID) 
                                                    VALUES ('{0}','{1}',{2},GetDate(),'{3}','{4}','{5}','{6}')",
                                       varDoc, varMaterialCode, varQty, Utils.uContext.UserIDk__BackingField, varSerialNumber, varDateCode, tsdm_id);
                Utils.NMS.Exectransql(Utils.uContext, strDocDel, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);

                string strDoc = string.Format(@"select count(1) from T_Bllb_StorageDocMaterial_tsdm a left join T_Bllb_StorageDoc_tbsd b
                                                        on a.S_Doc_NO = b.S_Doc_NO
                                                        where a.s_doc_no = '{0}' and qty < plan_qty", varDoc);
                DataTable dtDoc = Utils.NMS.QueryDataTable(Utils.uContext, strDoc, new CIT.WMS.MESWcf.CmdParameter[0]);
                string status = "2";
                if (dtDoc == null || dtDoc.Rows.Count == 0)
                {
                    status = "3";
                }
                string strDocUp = string.Format(@"update T_Bllb_StorageDoc_tbsd set Status = '{1}' where s_doc_no = '{0}'", varDoc, status);
                Utils.NMS.Exectransql(Utils.uContext, strDocUp, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// 成品仓库备料
        /// </summary>
        /// <param name="varDoc"></param>
        /// <param name="varStorage"></param>
        /// <param name="outMeg"></param>
        /// <param name="outDateCode"></param>
        /// <returns></returns>
        public static bool QueryInfo(string varDoc, string varStorage, out string outMeg, out string outDateCode, out string outMaterialCode)
        {
            outMeg = "";
            outDateCode = "";
            outMaterialCode = "";
            string strDoc = string.Format(@"select * from T_Bllb_StorageDocMaterial_tsdm where s_doc_no = '{0}' and qty < plan_qty order by rowNumber", varDoc);
            DataTable dtDoc = Utils.NMS.QueryDataTable(Utils.uContext, strDoc, new CIT.WMS.MESWcf.CmdParameter[0]);
            if (dtDoc == null || dtDoc.Rows.Count == 0)
            {
                outMeg = "单据发料完成";
                return false;
            }
            string strSql = string.Format(@"select materialcode,Location_sn, dateCode from T_Bllb_StockInfo_tbsi where materialcode = '{0}' and storage_sn = '{1}' and Lock_Flag = '0' and Location_sn <> '' and dateCode <>''  order by dateCode",
                dtDoc.Rows[0]["MaterialCode"], varStorage);
            DataTable dtSql = Utils.NMS.QueryDataTable(Utils.uContext, strSql, new CIT.WMS.MESWcf.CmdParameter[0]);
            if (dtSql != null && dtSql.Rows.Count > 0)
            {
                outMeg = @"库位:" + dtSql.Rows[0]["Location_sn"] + "  料号:" + dtSql.Rows[0]["materialcode"]
                        + " DateCode:" + dtSql.Rows[0]["dateCode"];
                outDateCode = dtSql.Rows[0]["dateCode"].ToString();
                outMaterialCode = dtDoc.Rows[0]["MaterialCode"].ToString();
                return true;
            }
            else
            {
                outMeg = dtDoc.Rows[0]["MaterialCode"] + "缺料";
                return false;
            }
        }

        /// <summary>
        /// 出库移库存表
        /// </summary>
        /// <returns></returns>
        public static bool MoveStock(string varBox, string varDocNumber)
        {
            try
            {
                string strMove = string.Format(@"INSERT INTO T_Bllb_StockInfo_Move_tbsm
                                                     select *, '{1}' DocNumber from  T_Bllb_StockInfo_tbsi where serialnumber = '{0}'", varBox, varDocNumber);
                Utils.NMS.Exectransql(Utils.uContext, strMove, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);

                string strDe = string.Format(@"delete T_Bllb_StockInfo_tbsi where serialnumber = '{0}'", varBox);
                Utils.NMS.Exectransql(Utils.uContext, strDe, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public static bool UpdateStatuFlag()
        {
            try
            {
                string strsql = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl set Status_Flag='1'
WHERE Location_SN IN
(select Location_SN from  wms_T_Inventory a inner join T_Bllb_Storage_tbs b
on a.Storage_SN = b.Storage_SN
where b.Storage_Type = '201')");
                Utils.NMS.Exectransql(Utils.uContext, strsql, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 成品入库
        /// </summary>
        /// <param name="varBoxSn"></param>
        /// <returns></returns>
        public static bool ProductInStock(string billNo, string storageSN, string areaSN, string locationSN, string boxNo, int qty)
        {

            var result = false;
            try
            {
                var sqlSearch = string.Format(@"SELECT st.BillNo,sr.RowNo,sr.PartNumber,st.Ext1 AS Version,st.Ext2 AS DateCode,st.Ext3 AS LotNo,st.RelatedBill  FROM dbo.wms_T_InStock  st
LEFT JOIN dbo.wms_T_InStockRows sr ON sr.BillNo = st.BillNo
WHERE st.BillNo='{0}'", billNo);

                var infoDt = Utils.NMS.GetDataTable(sqlSearch);

                if (infoDt.Rows.Count == 0)
                {
                    return false;
                }

                var info = infoDt.Rows[0];

                var sql_Update = string.Format(@"UPDATE dbo.wms_T_InStockRowDetails SET Status='H' WHERE BoxNo='{0}' AND Status='0';", boxNo);

                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_InStockRowDetails]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[BillNo]
           ,[RowNo]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[LotNo]
           ,[BoxNo]
           ,[Creator]
           ,[CreateTime]
           ,[PutawayQty]
           ,[Status]
           ,[RealQty]
           ,[RelatedBill])
		   VALUES(
		   NEWID(),
		   '{0}',
		   '{1}',
		   '{2}',
		   '{3}',
		   '{4}',
		   '{5}',
		   '{6}',
		   '{7}',
		   '{8}',
		   '{9}',
		   '{10}',
		   GETDATE(),
		   '{11}',
		   '0',
		   '{11}',
		   '{12}');", storageSN, areaSN, locationSN, billNo, info["RowNo"].ToString(), info["PartNumber"].ToString(),
                    info["Version"].ToString(), info["DateCode"].ToString(), info["LotNo"].ToString(), boxNo, Utils.uContext.UserNamek__BackingField,
                 qty, info["RelatedBill"].ToString());

                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_InStockRows]
   SET  [RealQty] = (SELECT SUM([PutawayQty]) FROM [wms_T_InStockRowDetails] WHERE BillNo='{0}' AND RowNo='{1}')
 WHERE  BillNo='{0}' AND RowNo='{1}';
 UPDATE dbo.wms_T_InStock SET Updator='{2}',UpdateTime=GETDATE() WHERE BillNo='{0}';", billNo, info["RowNo"].ToString(), Utils.uContext.UserNamek__BackingField);

                var sql3 = string.Empty;
                var sql4 = string.Empty;

                var sqlInv = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}'", locationSN);

                var invDt = Utils.NMS.GetDataTable(sqlInv);

                if (invDt.Rows.Count > 0)
                {
                    sql4 = string.Format(@"UPDATE dbo.wms_T_Inventory SET Qty=ISNULL(Qty,0)+{1} WHERE Location_SN='{0}';", locationSN, qty);
                }
                else
                {
                    sql3 = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);

                    sql4 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[Qty]
           ,[SerialNumber]
           ,[EnterTime]
           ,[Status]
           ,[LotNo]
           ,[BoxNo])
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,''
           ,GETDATE()
           ,'1'
           ,'{8}'
           ,'{9}');", Guid.NewGuid(), storageSN, areaSN, locationSN, info["PartNumber"].ToString(), info["Version"].ToString(),
                        info["DateCode"].ToString(), qty, info["LotNo"].ToString(), boxNo);
                }

                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}');", Guid.NewGuid(), storageSN, areaSN, locationSN, info["PartNumber"].ToString(),
                        qty, Utils.uContext.UserNamek__BackingField, "I", boxNo, info["LotNo"].ToString(), info["RelatedBill"].ToString(), billNo);

                //Utils.NMS.ExcuteSQL(insertHis_Sql);
                // result = true;

                var sqlCmd = (sql_Update + sql + sql2 + sql3 + sql4 + insertHis_Sql).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
+ @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excuRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excuRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }

            return result;
        }

        public static bool CheckInStockAndUpdate(string billNo)
        {
            var sql = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRows]
WHERE BillNo='{0}' AND PlanQty>ISNULL(RealQty,0)", billNo);
            var dt = Utils.NMS.GetDataTable(sql);
            var result = false;
            if (dt.Rows.Count == 0)
            {
                string updateSql = string.Format(@"UPDATE [dbo].[wms_T_InStock]
               SET [Status] = '1'
                  ,[Updator] = '{0}'
                  ,[UpdateTime] = '{1}'
             WHERE BillNO='{2}'", Utils.uContext.UserNamek__BackingField, DateTime.Now, billNo);
                Utils.NMS.ExcuteSQL(updateSql);
                result = true;
            }
            return result;
        }


        public static DataTable GetStorageConfirmInfo(string billNo)
        {

            var sql = string.Format(@"  SELECT sr.partNumber,sr.planQty,sr.realQty,st.BillType  FROM dbo.wms_T_InStock st
LEFT JOIN dbo.wms_T_InStockRows sr ON st.BillNo=sr.BillNo
WHERE st.BillNo='{0}'", billNo);
            var result = Utils.NMS.GetDataTable(sql);
            return result;
        }


        public static bool InStockStorageConfirm(string billNo, out string relatedBill)
        {
            var result = false;
            relatedBill = string.Empty;
            try
            {
                string selectSql = string.Format(@"select * from [dbo].[wms_T_InStock] where billNo='{0}'", billNo);

                DataTable dt = Utils.NMS.GetDataTable(selectSql);

                if (dt.Rows.Count > 0)
                {
                    relatedBill = dt.Rows[0]["RelatedBill"].ToString();
                }

                string updateSql = string.Format(@"UPDATE [dbo].[wms_T_InStock]
               SET [Status] = '1'
                  ,[Updator] = '{0}'
                  ,[UpdateTime] = GETDATE()
             WHERE BillNO='{1}'", Utils.uContext.UserNamek__BackingField, billNo);
                Utils.NMS.ExcuteSQL(updateSql);
                result = true;
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static bool InStockRETStorage(string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"select it.relatedbill,sr.partNumber,sr.RealQty from [dbo].[wms_T_InStock] it
left join [dbo].[wms_T_InStockRows] sr on it.Billno=sr.billNo where it.BillNo='{0}'", billNo);

                var dt = Utils.NMS.GetDataTable(sql);

                if (dt.Rows.Count > 0)
                {
                    StringBuilder sb = new StringBuilder();

                    var relatedbill = dt.Rows[0]["relatedbill"].ToString();
                    foreach (DataRow dr in dt.Rows)
                    {
                        var sqltemp = String.Format(@"update [dbo].[wms_T_OutStockRows] set SelectQty={0},RealQty={0} where billno in (select billno from [wms_T_OutStock] where relatedbill='{1}') and productNumber='{2}'", dr["RealQty"], dr["relatedbill"], dr["partNumber"]);
                        sb.Append(sqltemp);
                    }
                    var updateSql = String.Format(@"Update wms_T_OutStock set Status=1 where RelatedBill='{0}'", relatedbill);
                    sb.Append(updateSql);

                    var sql2 = sb.ToString();
                    Utils.NMS.ExcuteSQL(sql2);
                }
                result = true;
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        /// <summary>
        /// 获取库位类型
        /// </summary>
        /// <returns></returns>
        public static DataTable GetStandardType(string Area_SN)
        {
            string sql;

            sql = @"select DISTINCT Area_Standard as StandardSN from T_Bllb_StorageLocation_tbsl where Area_SN = '{0}'";
            //sql = @"SELECT StandardSN,StandardName FROM wms_B_AreaStandard WHERE StandardSN LIKE 'FG%'";
            sql = string.Format(sql, Area_SN);
            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static DataTable GetStandardInfoList(string storageSN, string standardType)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(storageSN))
            {
                sql = string.Format(@"SELECT a.Storage_SN, l.Area_SN, l.Location_SN, l.Area_Standard FROM T_Bllb_StorageArea_tbsa a
INNER JOIN T_Bllb_StorageLocation_tbsl l
ON a.Area_SN = l.Area_SN
AND (l.Status_Flag = 0 OR l.Status_Flag IS NULL)
WHERE  l.Area_Standard = '{0}'
ORDER BY a.Area_Order,Area_SN,Location_SN", standardType);
            }
            else
            {
                sql = string.Format(@"SELECT a.Storage_SN, l.Area_SN, l.Location_SN, l.Area_Standard FROM T_Bllb_StorageArea_tbsa a
INNER JOIN T_Bllb_StorageLocation_tbsl l
ON a.Area_SN = l.Area_SN
AND (l.Status_Flag = 0 OR l.Status_Flag IS NULL)
WHERE a.Storage_SN = '{0}' 
AND l.Area_Standard = '{1}'
ORDER BY a.Area_Order,Area_SN,Location_SN", storageSN, standardType);
            }

            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static DataTable GetStandardInfoListTop(string storageSN, string areaSN, string standardType, int top)
        {
            string sql = string.Empty;
            if (string.IsNullOrEmpty(storageSN))
            {
                sql = string.Format(@"SELECT top {1} a.Storage_SN, l.Area_SN, l.Location_SN, l.Area_Standard FROM T_Bllb_StorageArea_tbsa a
INNER JOIN T_Bllb_StorageLocation_tbsl l
ON a.Area_SN = l.Area_SN
AND (l.Status_Flag = 0 OR l.Status_Flag IS NULL)
WHERE  l.Area_Standard = '{0}' AND l.Area_SN='{2}'
ORDER BY a.Area_Order,Area_SN,Location_Order,Location_SN", standardType, top, areaSN);
            }
            else
            {
                sql = string.Format(@"SELECT top {2} a.Storage_SN, l.Area_SN, l.Location_SN, l.Area_Standard FROM T_Bllb_StorageArea_tbsa a
INNER JOIN T_Bllb_StorageLocation_tbsl l
ON a.Area_SN = l.Area_SN
AND (l.Status_Flag = 0 OR l.Status_Flag IS NULL)
WHERE a.Storage_SN = '{0}' 
AND l.Area_Standard = '{1}' AND l.Area_SN='{3}'
ORDER BY a.Area_Order,Area_SN,Location_Order,Location_SN", storageSN, standardType, top, areaSN);
            }

            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static DataTable GetInStockInfo(string billNo)
        {
            string sql = string.Format(@"SELECT st.*,sr.PartNumber FROM dbo.wms_T_InStock  st
LEFT JOIN dbo.wms_T_InStockRows sr ON sr.BillNo = st.BillNo
WHERE st.BillNo='{0}'", billNo);
            var result = Utils.NMS.GetDataTable(sql);
            return result;
        }

        public static bool ProductInStockByTorr(string billNo, string storageSN, string areaSN, string locationSN, int qty)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_InStockRows] SET RealQty= ISNULL(RealQty,0) +{0} WHERE BillNo='{1}';
UPDATE dbo.wms_T_InStock SET Updator='{2}',UpdateTime=GETDATE() WHERE BillNo='{1}';", qty, billNo, Utils.uContext.UserNamek__BackingField);
                var sql234 = string.Format("SELECT * FROM T_Bllb_StorageLocation_tbsl WHERE Location_SN='{0}'", locationSN);

                var dt1234 = Utils.NMS.GetDataTable(sql234);
                var status = "";

                if (dt1234.Rows.Count > 0)
                {
                    status = dt1234.Rows[0]["Status_Flag"].ToString();
                }
                if (storageSN.StartsWith("FG"))
                {
                    if (status == "1")
                    {
                        return false;
                    }
                }
                var sql2 = string.Format(@"UPDATE T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);

                //Utils.NMS.ExcuteSQL(sql);
                //Utils.NMS.ExcuteSQL(sql2);

                string row_Sql = string.Format(@"SELECT sr.*,st.RelatedBill,st.Ext1,st.Ext2,st.Ext3  FROM [dbo].[wms_T_InStockRows] sr
LEFT JOIN dbo.wms_T_InStock st ON sr.BillNo=st.BillNo
WHERE sr.BillNo='{0}'", billNo);

                DataTable dt = Utils.NMS.GetDataTable(row_Sql);
                var row = dt.Rows[0];

                var version = row["Ext1"].ToString();
                var datecode = row["Ext2"].ToString();
                var lotno = row["Ext3"].ToString();
                if (string.IsNullOrEmpty(lotno))
                {
                    lotno = datecode;
                }

                string insert_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[EnterTime]
           ,[Status],Version,DateCode,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,GETDATE()
           ,'{6}','{7}','{8}','{9}');", Guid.NewGuid(), storageSN, areaSN, locationSN, row["PartNumber"].ToString(), qty, "1", version, datecode, lotno);

                string insertHis_Sql = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO],RelatedBill1,RelatedBill2,LotNo)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}','{8}','{9}','{10}');", Guid.NewGuid(), storageSN, areaSN, locationSN, row["PartNumber"].ToString(),
                        qty, Utils.uContext.UserNamek__BackingField, "I", row["RelatedBill"].ToString(), billNo, lotno);

                var sqlCmd = (sql + sql2 + insert_Sql + insertHis_Sql).TrimEnd(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
                + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static bool InStockFinish(string billNo, string billType)
        {
            var result = false;
            try
            {
                var sql = string.Empty;
                if (billType.Equals("手工成品入库"))
                {
                    sql = string.Format(@"
INSERT INTO [dbo].[wms_T_InStockRowDetails]
           ([Guid]
           ,[BillNo]
           ,[RowNo]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[LotNo]
           ,[Creator]
           ,[CreateTime]
           ,[PutawayQty]
           ,[Status]
           ,[RealQty]
           ,[RelatedBill])
SELECT NEWID(),sr.BillNo,sr.RowNo,sr.PartNumber,st.Ext1,st.Ext2,st.Ext3,'{0}',GETDATE(),sr.RealQty,'0',sr.RealQty,st.RelatedBill FROM 
dbo.wms_T_InStockRows sr
LEFT JOIN dbo.wms_T_InStock st ON st.BillNo = sr.BillNo
WHERE sr.BillNo='{1}' and st.Status = '0';", Utils.uContext.UserNamek__BackingField, billNo);
                }

                string updateSql = string.Format(@"UPDATE [dbo].[wms_T_InStock]
               SET [Status] = '1'
                  ,[Updator] = '{0}'
                  ,[UpdateTime] = GETDATE()
             WHERE BillNO='{1}' and Status = '0';", Utils.uContext.UserNamek__BackingField, billNo);


                var sqlCmd = (sql + updateSql).Trim(';');


                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        /// <summary>
        /// 返回值，0表示LocationSN没有被使用，1表示LocationSN已使用，2表示LocationSN不存在
        /// </summary>
        /// <param name="locationSN"></param>
        /// <returns></returns>
        public static int GetLocationSNStatus(string locationSN)
        {
            var result = 0;
            var sql = string.Format("SELECT * FROM T_Bllb_StorageLocation_tbsl WHERE Location_SN='{0}'", locationSN);

            var dt = Utils.NMS.GetDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                sql = string.Format("SELECT * FROM wms_T_Inventory WHERE Location_SN='{0}'", locationSN);
                dt = Utils.NMS.GetDataTable(sql);

                if (dt.Rows.Count > 0)
                {
                    result = 1;
                }
                else
                {
                    result = 0;
                }
            }
            else
            {
                result = 2;
            }
            return result;
        }

        /// <summary>
        /// 0表示没有，1表示有
        /// </summary>
        /// <param name="partNumber"></param>
        /// <returns></returns>
        public static int GetLocationSNStatus(string locationSN, string partNumber)
        {
            var result = 0;
            var sql = string.Format("SELECT * FROM wms_T_Inventory WHERE Location_SN='{0}' and PartNumber = '{1}'", locationSN, partNumber);

            var dt = Utils.NMS.GetDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                result = 1;
            }

            return result;
        }


        public static DataTable GetAreaSN(string storageSN)
        {
            var sql = string.Empty;
            if (string.IsNullOrEmpty(storageSN))
            {
                sql = string.Format(@"select DISTINCT b.Area_SN  from T_Bllb_StorageLocation_tbsl a 
left join T_Bllb_StorageArea_tbsa b on a.Area_SN = b.Area_SN
 left join T_Bllb_Storage_tbs c on b.Storage_SN = c.Storage_SN
 LEFT JOIN wms_T_StorageRegion d ON c.Region_SN=d.Region_SN
 WHERE d.Region_Name='{0}' AND ISNULL(a.Status_Flag,0)<>1 order by b.Area_SN", Utils.uContext.CompanyNamek__BackingField);
            }
            else
            {
                sql = string.Format(@"select DISTINCT b.Area_SN  from T_Bllb_StorageLocation_tbsl a 
left join T_Bllb_StorageArea_tbsa b on a.Area_SN = b.Area_SN
 left join T_Bllb_Storage_tbs c on b.Storage_SN = c.Storage_SN
 LEFT JOIN wms_T_StorageRegion d ON c.Region_SN=d.Region_SN
 WHERE d.Region_Name='{0}' AND c.Storage_SN='{1}' AND ISNULL(a.Status_Flag,0)<>1 order by b.Area_SN", Utils.uContext.CompanyNamek__BackingField, storageSN);
            }

            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static string GetProductNumberByBillNo(string billNo)
        {
            string sql = string.Format(@"SELECT * FROM  dbo.wms_T_InStockRows WHERE BillNo='{0}'", billNo);

            var dt = Utils.NMS.GetDataTable(sql);

            var result = string.Empty;
            if (dt.Rows.Count > 0)
            {
                result = dt.Rows[0]["PartNumber"].ToString();
            }
            return result;
        }

        public static DataTable GetInventoryInfo(string locationSN)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}' and Status='1'", locationSN);
            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static string GetFatp(string partNumber)
        {
            string sql = string.Format(@"SELECT * FROM dbo.MdcdatMaterial WHERE  MaterialCode='{0}' ", partNumber);
            var result = Utils.NMS.GetDataTable(sql);

            return result.Rows[0]["IncomingType"].ToString();
        }

        public static DataTable GetInventoryInfo(string locationSN, string PartNumber)
        {
            string sql = string.Format(@"SELECT * FROM dbo.wms_T_Inventory WHERE Location_SN='{0}' and PartNumber='{1}' and Status='1'", locationSN, PartNumber);
            var result = Utils.NMS.GetDataTable(sql);

            return result;
        }

        public static int GetInStockBoxsCount(string billNo)
        {
            var sql = string.Format(@"SELECT count(1) as Count FROM dbo.wms_T_InStockRowDetails WHERE BillNo='{0}'", billNo);
            var dt = Utils.NMS.GetDataTable(sql);

            var result = 0;
            try
            {

                result = Convert.ToInt32(dt.Rows[0]["Count"]);
            }
            catch
            {

            }
            return result;
        }


        public static bool InStockIsCanAddQty(string billNo, string productNumber, int qty)
        {
            var sql = string.Format(@"SELECT * FROM dbo.wms_T_InStockRows WHERE PartNumber='{0}' AND ISNULL(PlanQty,0)>= ISNULL(RealQty,0)+{1} AND BillNo='{2}'", productNumber, qty, billNo);

            var dt = Utils.NMS.GetDataTable(sql);
            var result = false;
            if (dt != null && dt.Rows.Count > 0)
            {
                result = true;
            }
            return result;
        }

        /// <summary>
        /// 获取箱号信息
        /// </summary>
        /// <param name="boxNo"></param>
        /// <returns></returns>
        public static DataTable GetBoxInfo(string boxNo)
        {
            var sql = string.Format(@"SELECT * FROM dbo.wms_T_InStockRowDetails WHERE BoxNo='{0}'", boxNo);
            var dt = Utils.NMS.GetDataTable(sql);

            return dt;
        }

        /// <summary>
        /// 确认仓库类型
        /// </summary>
        /// <param name="boxNo"></param>
        /// <returns></returns>
        public static bool IsNGStorage(string storage_SN)
        {
            var sql = string.Format(@"SELECT * FROM T_Bllb_Storage_tbs WHERE Storage_SN='{0}'", storage_SN);
            var dt = Utils.NMS.GetDataTable(sql);

            if (dt.Rows[0]["Storage_Type"].ToString() == "202")
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 调用销售退货Proc
        /// </summary>
        /// <param name="billno"></param>
        /// <param name="userName"></param>
        /// <param name="storageSN"></param>
        /// <param name="areaSN"></param>
        /// <param name="locationSN"></param>
        /// <param name="boxNo"></param>
        /// <param name="qty"></param>
        /// <param name="isNG"></param>
        /// <returns></returns>
        public static int SalesBackQuery(string billno, string userName, string storageSN,
                                         string areaSN, string locationSN, string boxNo, decimal qty, bool isNG)
        {
            string sqlcmd = string.Format(@"begin 
                                            DECLARE @ret INT;
                                            exec @ret=PDA_SalesBack_query @userName=N'{0}',@billNo=N'{1}',
                                                    @storageSN=N'{2}',@areaSN=N'{3}',@locationSN=N'{4}',@boxNo=N'{5}',@qty={6},@isNG={7};
                                            SELECT @ret 
                                            end",
                                            userName, billno, storageSN, areaSN, locationSN, boxNo, qty.ToString(CultureInfo.InvariantCulture), isNG);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return Convert.ToInt16(result.Rows[0][0]);
        }

        /// <summary>
        /// 获取所有未入库的入库单号
        /// </summary>
        /// <returns>未入库的入库单号</returns>
        public static DataTable GetInStockProductForCheck()
        {
            string sqlcmd = string.Format(@"
                   SELECT a.BillNo
                     FROM [dbo].[wms_T_InStock] a
                    WHERE status = '0'
                      AND EXISTS (SELECT BillNo FROM [dbo].[wms_T_InStockRowDetails] WHERE BillNo = a.BillNo)");
            var result = Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
            return result;
        }

        /// <summary>
        /// 获取入库单下所有箱号
        /// </summary>
        /// <param name="billno">入库单号</param>
        /// <returns>入库单下所有箱号</returns>
        public static DataTable GetBoxNoList(string billno)
        {
            var sql = string.Format(@"SELECT DISTINCT BoxNo FROM [dbo].[wms_T_InStockRowDetails] WHERE BillNo='{0}'", billno);
            var dt = Utils.NMS.GetDataTable(sql);
            return dt;
        }
    }
}
